package cn.test.dao.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import cn.test.model.Account;

public class DBImpl implements DB {

	@Override
	public void insertAccount(Account account) {
		Connection cn = DBUtil.getConnect();
		String str="insert into t_account(name,account,money)value(?,?,?)";
		PreparedStatement ps = null;
		
		try {
			ps = (PreparedStatement)cn.prepareStatement(str);
			ps.setString(1, account.getName());
			ps.setString(2, account.getAccount());
			ps.setFloat(3, (float)account.getMoney());
			ps.execute();
			System.out.println("insert successfully!");
		} catch (Exception e) {
			System.out.println("insert failure!");
		}finally {
			try {
				ps.close();
				cn.close();
			} catch (Exception e2) {
				System.out.println("close failure!");
			}
		}
	}

	@Override
	public void deleteAccount(String account) {
		Connection cn1 = DBUtil.getConnect();
		String str = "delete from t_account where account=?";
		PreparedStatement ps1 = null;
		
		try {
			ps1 = (PreparedStatement)cn1.prepareStatement(str);
			ps1.setString(1, account);
			if(ps1.executeUpdate() == 0) {
				System.out.println("delete failure!");
			}else {
				System.out.println("delete successfully");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				ps1.close();
				cn1.close();
			} catch (Exception e2) {
				System.out.println("close failure");
			}
		}
	}

	@Override
	public void updateAccount(Account account) {
		Connection cn2 = DBUtil.getConnect();
		String str = "update t_account set name=?,,money=?where account=?";
		PreparedStatement ps2 = null;
		
		try {
			ps2 = (PreparedStatement)cn2.prepareStatement(str);
			ps2.setString(1, account.getName());
			ps2.setFloat(2, (float)account.getMoney());
			ps2.setString(3, account.getAccount());
			
			if (ps2.executeUpdate() == 0) {
				System.out.println("update failure!");
			} else {
				System.out.println("update successfully!");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				ps2.close();
				cn2.close();
			} catch (Exception e2) {
				System.out.println("close failure");
			}
		}
	}

	@Override
	public ArrayList<Account> selectAccount(String account) {
		ArrayList<Account> list = new ArrayList<Account>();
		Connection cn3 = DBUtil.getConnect();
		String str = "select * from t_account where account=?";
		PreparedStatement ps3 = null;
		ResultSet result = null;
		
		try {
			ps3=(PreparedStatement)cn3.prepareStatement(str);
			ps3.setString(1, account);
			result = ps3.executeQuery();
			while(result.next()) {
				list.add(new Account(result.getString("name"),result.getString("account"),result.getString("money")));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				ps3.close();
				cn3.close();
			} catch (Exception e2) {
				System.out.println("close failure");
			}
		}
		return list;
	}
	public static void main(String[] args) {
		DBImpl db = new DBImpl();
		ArrayList<Account> list = db.selectAccount("A002");
		for (Account account : list) {
			System.out.println(account);
		}
	}

}
